package poi;

import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jdom.Document;
import org.jdom.Element;
import org.jdom.input.SAXBuilder;

public class POITest {

    /**
     * @param args
     */
    public static void main(String[] args) {
        
        try {
            String filename="report_779";
            SAXBuilder sb = new SAXBuilder();
            Document doc = sb.build(new File("D:\\My Documents\\Downloads\\"+filename+".xml"));

            String excelname = "D:\\My Documents\\Downloads\\"+filename+".xlsx";
            String sheetname = "test";
            
            Workbook wb = new XSSFWorkbook();
            Sheet sheet = wb.createSheet(sheetname);

            Element root = doc.getRootElement();
            List list = root.getChildren();

            CellStyle headingCellStyle = wb.createCellStyle();
            headingCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
            Font headingCellFont = wb.createFont();
            headingCellFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
            headingCellStyle.setFont(headingCellFont);
            Row row;
            row = sheet.createRow((short) 0);
            Element element=null;
            for(Element el : (List<Element>)list){
                if(el.getName().equalsIgnoreCase("row")){
                    element = el;
                    break;
                }
            }
            List posList = writeHeader(element, row, headingCellStyle);
            System.out.println("total row "+list.size());
            if (posList != null && posList.size() > 0) {

                short rowIndex = 0;
                System.out.println("start row loop");
                for (int j = 1; j < list.size(); j++) {
                    if(j%1000 ==0){
                        System.out.println("done row "+j);
                    }
                    
                    if(j > 13000){
                        System.out.println("current row "+j);
                    }
                    
                    Element section = (Element) list.get(j);
                    List sect = section.getChildren();
                    row = sheet.createRow((short) ++rowIndex);
                    for (int colIndex = 0; colIndex < posList.size(); colIndex++) {
                        int[] poss = (int[]) posList.get(colIndex);
                        Cell cell = row.createCell((short) colIndex);
                        if (poss[0] == -1) {
                            cell.setCellValue("");
                        } else {
                            String value = ((Element) sect.get(poss[0]))
                                    .getText();
                            String tvalue = value;
                            if (poss[1] == 1) {
                                try {
                                    tvalue = formatNum(value);
                                } catch (Exception e) {

                                }
                            } else if (poss[1] == 2) {
                                try {
                                    tvalue = formatNum(value);
                                } catch (Exception e) {

                                }
                            }
                            cell.setCellValue(tvalue);
                        }
                    }

                }
                
                System.out.println("end row loop");
            }
            
            System.out.println("Start write out");
            FileOutputStream fileOut = new FileOutputStream(excelname);
            wb.write(fileOut);
            fileOut.close();
            System.out.println("End write out");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    static List writeHeader(Element element,Row row,CellStyle headingCellStyle){
        List nameMapping=null;
        
        
        ArrayList  posList=new ArrayList();     
        if (nameMapping == null || nameMapping.size() == 0) {
                for (int i = 0; i < element.getChildren().size(); i++) {
                    String columnName = ((Element) element.getChildren()
                            .get(i)).getName();
                    if (columnName == null)
                        columnName = "";
                    else if (columnName.length() >= 1)
                        columnName = columnName.substring(0, 1)
                                .toUpperCase()
                                + columnName.substring(1);
                    Cell cell = row.createCell((short) (i));
                    cell.setCellValue(columnName);
                    cell.setCellStyle(headingCellStyle);
                    posList.add(new int[]{i,0});
                }
            }
        else {
            for(int i=0;i<nameMapping.size();i++){
                String[] names=(String[])nameMapping.get(i);
                int   pos=-1;
                for (int j = 0; j < element.getChildren().size(); j++) {
                    String columnName = ((Element) element.getChildren()
                            .get(j)).getName();
                    if (columnName == null)
                        columnName = "";
                    if(columnName.equals(names[0])){
                        pos=j;
                        break;
                    }
                }
                
                int type=0;
                if("Long".equalsIgnoreCase(names[2]))
                    type=1;
                else if("Double".equalsIgnoreCase(names[2]))
                    type=2;             
                posList.add(new int[]{pos,type});
            }
            
            for(int i=0;i<nameMapping.size();i++){
                String[] names=(String[])nameMapping.get(i);
                if(names[1] == null)
                    names[1]="";
                Cell cell = row.createCell((short) (i));
                cell.setCellValue(names[1]);
                cell.setCellStyle(headingCellStyle);
            }
        }
        return posList;
    }
    
    static String formatNum(String str) {
        double strd = 0.0;

        try {
            strd = Double.parseDouble(str);
        } catch (NumberFormatException e) {
        }

        int tempint = (int) Math.round(strd * 100);
        strd = (double) tempint / 100;
        str = "" + strd;
        tempint = (int) Math.round(strd);
        if (tempint == strd)
            str = "" + tempint;
        else {
            if (str.indexOf(".") > -1) {
                str = str + "00";
                str = str.substring(0, str.indexOf(".") + 3);
            }
        }
        return str;
    }

}
